using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using SQLString.Classes;
using SQLString.AppResource;
using SQLString.Properties;

namespace SQLString.Dialogs
{
	///<SUMMARY>
	///************************************************************************************************************************************
	///*	DEVELOPER Code Mind Dumps
	///*	=========================
	///************************************************************************************************************************************
	///*
	///*	Date						Developer		
	///*	"06/22/2008"				Jeff Hamlin/jeffrey.hamlin@gmail.com/this.notNULL@gmail.com
	///*
	///*	Code Mind Dump Name
	///*	How this works...
	///*
	///*	Code Mind Dump Detail
	///*	<CODE>
	///*	This manages the application settings.  It stores the 5 critical pieces of information the app needs for the CodeKeep:
	///			1.) Dtaabase Server Name
	///			2.) Database Server Catalog
	///			3.) Use Integrated Security (DEFAULT) or the optional user id/password
	///			OPTIONAL:
	///				a1.) User ID
	///				a2.) User Pwd	- not encrypted yet.
	/// 
	///		On the first run, the application must be able to create the tables needed.  Store a flag for this.
	///		Any modifications to properties will involve the following files:
	/// 
	/// 
	///		Properties Section:
	///			Settings.Settings			-	Resource file
	///			Settings.Designer.cs		-	Contains "namespace SQLString.Properties" and "internal sealed partial class Settings : global::System.Configuration.ApplicationSettingsBase"
	///		SQLString.dll.config			-	in the BIN directory, a XML DLL generated from app.Config in the root directory.
	///		fSQLString.Settings.Designer.cs	-	the designer for this form has the databinded properties in the InitializeComponent()
	///*	</CODE>
	///*
	///*	<EXAMPLE>
	///*				List<books> janet = new List<books>();
	///*	</EXAMPLE>
	///*
	///************************************************************************************************************************************
	///</SUMMARY>




	public partial class fSQLStringSettings : Form
	{
		Settings MUS=null;
		CommonFuntions FUNC=null;
		DatabaseObjects DBOCollection=null;	//local class of database objects for management
		SQLResource SQLRes=null;

		#region Form Properties - ONE indicates Application Property Status, the SECOND provides the ODBC connection
		Boolean bAppStatus = false;
		public Boolean AppPropertyStatus	//	A quick way to know if all is good without iterating thru thru the user props
		{
			get
			{
				return bAppStatus;
			}
			set
			{
				bAppStatus = value;
			}
		}
		string szDBConnectionString = string.Empty;
		public string Connection
		{
			get
			{
				return szDBConnectionString.Trim();
			}
			set
			{
				szDBConnectionString = value.Trim();
			}
		}
		#endregion

		#region Form Constructor
		public fSQLStringSettings(SQLResource _res)
		{

			InitializeComponent();
			MUS = new Settings();
			SQLRes = _res;
			DBOCollection = new DatabaseObjects(this,SQLRes);
			FUNC = new CommonFuntions();
		}
		#endregion

		#region Form Event Handlers


		/// <summary>
		/// Handles the Click event of the cmdSave control.
		/// </summary>
		/// <param name="sender">The source of the event.</param>
		/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
		/// **********************************************************************************************************
		/// *
		/// *	Coder:	jeffrey.hamlin
		/// *	EMAIL:	this.notNULL@gmail.com/this@jeffhamlin.com
		/// *	URL:	www.jeffhamlin.com
		/// *	
		/// *	SYS:EVENTYNE.7/6/2008
		/// *
		/// *********************************************************************************************************
		private void cmdSave_Click( object sender, EventArgs e )
		{
			try
			{
				MUS.DatabaseCatalog = txtDBCatalog.Text;
				MUS.Save();

				if (txtDBCatalog.Text.Length > 0)
				{
					MUS.DatabaseCatalog = txtDBCatalog.Text.Trim();
				}
				else
				{
					Monitor.sMsg("Please provide the Database CATALOG for the Database Connection.", MessageBoxIcon.Stop);
					return;
				}


				if (txtDBServer.Text.Length > 0)
				{
					MUS.DatabaseServer = this.txtDBServer.Text.Trim();
				}
				else
				{
					Monitor.sMsg("Please provide the Database SERVER for the Database Connection.", MessageBoxIcon.Stop);
					return;
				}

				if (chkSecurityOpt.CheckState == CheckState.Unchecked)
				{
					if (txtUserID.Text.Length > 0)
					{
						MUS.DatabaseUserID = txtUserID.Text.Trim();
					}
					else
					{
						Monitor.sMsg("For the Security Option chosen, please provide the User Id for the Database Connection.", MessageBoxIcon.Stop);
						return;
					}
					if (txtUserPWD.Text.Length > 0)
					{
						MUS.DatabaseUserPassword = txtUserPWD.Text.Trim();
					}
					else
					{
						Monitor.sMsg("For the Security Option chosen, please provide the User Password for the Database Connection.", MessageBoxIcon.Stop);
						return;
					}
				}
				//	storing the access method as either SecurityOption.Integrated	=	Integrated or as SecurityOption.UserDefined	=	User Security
				MUS.DatabaseAccessMethod = ( chkSecurityOpt.CheckState == CheckState.Checked ) ? SecurityOption.Integrated.ToString() : SecurityOption.UserDefined.ToString();
				MUS.Save();
				FUNC.SetCusror(cursorMode.Wait, this);
				if (( szDBConnectionString = GetSQLConnectString() ).Trim().Length > 0)
				{
					Monitor.sMsg("SQL Server Connection Verified for Provided Parameters.", MessageBoxIcon.Information);
					if (CheckForDBObjects(0) == false)
					{
						//	Display the missing tables to the user.
						FUNC.DisplayCommonList(DBOCollection.DBMissingObjects, "Missing Table Objects");
					}
				}
				MUS.Reload();
				SetApplicationSettings(0);

				cmdCreateDB.Enabled = this.AppPropertyStatus;
				cmdXMLArchive.Enabled = this.AppPropertyStatus;
				FUNC.SetCusror(cursorMode.Default, this);
			}
			catch (ArgumentException ex)
			{
				Monitor.sErrMsg(ex.Message);
			}
		}

		private void cmdClose_Click( object sender, EventArgs e )
		{
			this.Close();
		}

		private void fSQLStringSettings_Load( object sender, EventArgs e )
		{
			//	Read from the App Settings and populate the display
			
			txtDBCatalog.Text = MUS.DatabaseCatalog.Trim();
			txtDBServer.Text = MUS.DatabaseServer.Trim();
			if (MUS.DatabaseAccessMethod == SecurityOption.UserDefined.ToString())
			{
				chkSecurityOpt.CheckState = CheckState.Unchecked;
				txtUserID.Text = MUS.DatabaseUserID.Trim();
				txtUserPWD.Text = MUS.DatabaseUserPassword.Trim();
			}
			else
			{
				chkSecurityOpt.CheckState = CheckState.Checked;
				txtUserID.Text = string.Empty;
				txtUserPWD.Text = string.Empty;
			}
			SetApplicationSettings(1);
			cmdCreateDB.Enabled = this.AppPropertyStatus;
			cmdXMLArchive.Enabled = this.AppPropertyStatus;
		}

		#endregion

		#region Form Methods




		/// <summary>
		/// Determines if the application settings are setup, and sets the variable AppPropertyStatus to true or false.
		/// iMode is used to determine when to set the ConnectionSeting
		/// </summary>
		/// <param name="iMode">The i mode.</param>
		/// **********************************************************************************************************
		/// *	Coder:	jeffrey.hamlin
		/// *	EMAIL:	this.notNULL@gmail.com/this@jeffhamlin.com
		/// *	URL:	www.jeffhamlin.com
		/// *	
		/// *	SYS:EVENTYNE.7/6/2008
		/// *
		/// *********************************************************************************************************
		private void SetApplicationSettings(int iMode)
		{

			//	Setting the property
			if (iMode == 1)	// on a save operation no need to do this again
			{
				GetSQLConnectString();
			}
			if (MUS.DatabaseCatalog.Length > 0 && MUS.DatabaseServer.Length > 0)
			{
				if (this.Connection.Length > 0)
				{
					if (MUS.DatabaseAccessMethod == SecurityOption.Integrated.ToString())
					{
						this.AppPropertyStatus = true;
					}
					else if (MUS.DatabaseAccessMethod == SecurityOption.Integrated.ToString())
					{
						if (( MUS.DatabaseUserID.Length > 0 && MUS.DatabaseUserID != "user_id" )
							&& ( MUS.DatabaseUserPassword.Length > 0 && MUS.DatabaseUserPassword != "user_password" ))
						{
							this.AppPropertyStatus = true;
						}
						else
						{
							this.AppPropertyStatus = false;
						}
					}
				}
				else
				{
					this.AppPropertyStatus = false;
				}
			}
			else
			{
				this.AppPropertyStatus = false;
			}
		}
		
		
		
		
		
		private void chkSecurityOpt_CheckedChanged( object sender, EventArgs e )
		{
			if (chkSecurityOpt.CheckState == CheckState.Checked)
			{
				txtUserID.Enabled = false;
				txtUserPWD.Enabled = false;
			}
			else if (chkSecurityOpt.CheckState == CheckState.Unchecked)
			{
				txtUserID.Enabled = true;
				txtUserPWD.Enabled = true;
			}
		}




		
		
		
		/// <summary>
		/// Based on the Application settings class, this will retrieve the ConnectionString and return it to the main
		/// UI - it also performs validation on the string when changes are posted to the properties by the user.
		/// </summary>
		/// <returns></returns>
		/// **********************************************************************************************************
		/// *
		/// *	Coder:	jeffrey.hamlin
		/// *	EMAIL:	this.notNULL@gmail.com/this@jeffhamlin.com
		/// *	URL:	www.jeffhamlin.com
		/// *	
		/// *	SYS:EVENTYNE.6/22/2008
		/// *
		/// *********************************************************************************************************
		public string GetSQLConnectString()
		{
			string _usr = string.Empty;
			string _pwd = string.Empty;
			string _host = string.Empty;
			string _cat = string.Empty;

			string szServerCat = "Data Source=" + _host.Trim() + ";Initial Catalog=" + _cat.Trim() + ";";
			string szIntegrated = "Integrated Security=SSPI;Persist Security Info=False;Packet Size=4096;";
			string szUserPWD = ";user=" + _usr.Trim() + ";pwd=" + _pwd.Trim() + ";Connection Timeout=0;Packet Size=4096";
			string szConnection = string.Empty;


			if (MUS.DatabaseCatalog.Trim().Length > 0 && MUS.DatabaseServer.Trim().Length > 0)
			{
				//  Build Default Connection String
				_host = MUS.DatabaseServer.Trim();
				_cat = MUS.DatabaseCatalog.Trim();
				if (MUS.DatabaseAccessMethod.Trim() == SecurityOption.UserDefined.ToString())	//	user based security
				{
					if (MUS.DatabaseUserID.Trim().Length > 0 && MUS.DatabaseUserPassword.Trim().Length > 0)
					{
						//	Setup for user/pwd security
						_usr = MUS.DatabaseUserID.Trim();
						_pwd = MUS.DatabaseUserPassword.Trim();
						szConnection = "Data Source=" + _host.Trim() + ";Initial Catalog=" + _cat.Trim() + ";" + "user=" + _usr.Trim() + ";pwd=" + _pwd.Trim() + ";Connection Timeout=0;Packet Size=4096";
					}
					else
					{
						Monitor.sMsg("Selected security is based on the User Providing the USER ID and associated USER PASSWORD.  This information has not been provided.", MessageBoxIcon.Warning);
						//ask if they want to default to integrated security
					}
				}
				else if (MUS.DatabaseAccessMethod.Trim() == SecurityOption.Integrated.ToString())	//	INTEGRATED based security
				{
					szConnection = "Data Source=" + _host.Trim() + ";Initial Catalog=" + _cat.Trim() + ";" + szIntegrated;
				}
			}

			try
			{
				System.Data.SqlClient.SqlConnection dbConn = new SqlConnection(szConnection);
				dbConn.Open();
				if (dbConn.State == ConnectionState.Open)
				{
					dbConn.Close();
					this.Connection = szConnection;	// Update the property
					//	CHECK THE DB Object State
					if (CheckForDBObjects(0) == false)
					{
						return "-2";	//	ODBC Connection ok, but there were database issues (missing tables)
					}
					else
					{
						return szConnection;
					}
				}
				else
				{
					return "-1";	//ODBC Connection creation issues.
				}
			}
			catch (SQLString.Classes.SQLManagementException ex)
			{
				Monitor.sErrMsg("The Information Provided for the Database Parameters, did not allow a Connection for Verification.  Please Check Settings.\nERROR DETAIL:\n" + ex.Message);
				this.Connection = string.Empty;	// Update the property
				return String.Empty;
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				Monitor.sErrMsg("The Information Provided for the Database Parameters, did not allow a Connection for Verification.  Please Check Settings.\nERROR DETAIL:\n" + ex.Message);
				this.Connection = string.Empty;	// Update the property
				return String.Empty;
			}
			catch (Exception ex)
			{
				Monitor.sErrMsg(ex.Message);
				this.Connection = string.Empty;	// Update the property
				return String.Empty;
			}
			finally
			{

			}
		}


		/// <summary>
		/// Retrieves the current schema of db objects into the DBOCollection Class for the IsMissingOb method.
		/// </summary>
		/// <param name="iMode">0 = Normal Check to determine if there are any MISSING objects in selected DB.
		///						1 = User wants to Create a NEW DB, this mode indicates if the target DB has any
		///							existing objects which will be dropped.</param>
		/// <returns></returns>
		/// **********************************************************************************************************
		/// *	Coder:	jeffrey.hamlin
		/// *	EMAIL:	this.notNULL@gmail.com/this@jeffhamlin.com
		/// *	URL:	www.jeffhamlin.com
		/// *	
		/// *	SYS:EVENTYNE.6/22/2008
		/// *
		/// *********************************************************************************************************
		private bool CheckForDBObjects(int iMode)
		{
			SqlConnection dbConn = new SqlConnection(this.Connection);
			try
			{
				string szSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN " +DBOCollection.DBOList;
				int iCntr = 0;
				SqlCommand sqlCmd = new SqlCommand(szSQL);
				sqlCmd.Connection = dbConn;
				dbConn.Open();
				SqlDataReader sqlRdr = sqlCmd.ExecuteReader(CommandBehavior.Default);
				while (sqlRdr.Read())
				{
					DBOCollection.DBORetrievedTableObjectCollection[iCntr] = ( sqlRdr.IsDBNull(0) == false ) ? sqlRdr.GetString(0) : string.Empty;
					iCntr++;
				}

				if (iMode == 0)
				{
					int iCheck = 0;
					if (( iCheck = IsMissingObjects() ) > 0)
					{
						Monitor.sMsg("The SQL String System has " + iCheck.ToString() + " table objects which are missing from the configuration.  Please check the setup.", MessageBoxIcon.Warning);
						return false;
					}
					else
						return true;
				}
				else if(iMode==1)	// Pre-check for CreateNewDB
				{
					if (iCntr > 0)
					{
						return true;
					}
					else
						return false;
				}
				else
					return false;
			}
			catch (SQLString.Classes.SQLManagementException ex)
			{
				Monitor.sErrMsg("The Dtaabase Objects for SQLString doe not exist in the Catalog Specified.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				Monitor.sErrMsg("The Dtaabase Objects for SQLString doe not exist in the Catalog Specified.  \nERROR DETAIL:\n" + ex.Message);
				return false;
			}
			catch (Exception ex)
			{
				Monitor.sErrMsg(ex.Message);
				return false;
			}
			finally
			{

			}
		}

		/// <summary>
		/// Checkfors the missing database table objects in the DBOCollection Class.
		/// 
		/// <value>int</value>
		/// </summary>
		/// <returns>the number of missing objects.  If all nominal, should return 0</returns>
		/// *********************************************************************************************************
		///	Author: 	Jeff Hamlin
		/// *
		/// *********************************************************************************************************
		/// <author>jeffrey.hamlin</author>
		private int IsMissingObjects(  )
		{
			string[] szVerified = new string[] { "", "", "", "", "", "" };
			string[] szMissing = new string[] { "", "", "", "", "", "" };
			bool stat = false;
			int iMissing = 0;
			int iTotalOuterLoops = 0;
			int iTotalInnerLoops = 0;
			int iCntr = 0;
			foreach (string szREQUIRED in DBOCollection.DBOTableObjectCollection)
			{
				foreach (string szRETRIEVED in DBOCollection.DBORetrievedTableObjectCollection)
				{
					if (szRETRIEVED.ToLower().Trim() == szREQUIRED.Trim().ToLower())
					{
						DBOCollection.DBOVerifiedTableObjectCollection[iCntr] = szRETRIEVED.Trim();
						iCntr++;
						stat = true;
					}
					iTotalInnerLoops++;
				}
				if (iTotalInnerLoops == 6)
				{
					if (stat == false)
					{
						DBOCollection.DBMissingObjects[iMissing] = szREQUIRED.Trim();
						iMissing++;
					}
					else if (stat == true)
					{
						stat = false;
					}
					iTotalInnerLoops = 0;	// resetting the counter
				}
				iTotalOuterLoops++;
			}
			return iTotalOuterLoops - iCntr;
		}
		#endregion

		private void cmdCreateDB_Click( object sender, EventArgs e )
		{
			if (CheckForDBObjects(1))
			{
				if (MessageBox.Show("There are DB Objects in the current database which will be lost.  Continue with Creation Operation?",
					SQLRes.MessageCaption, MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
				{
					DBOCollection.RebuildDB();
				}
				else
				{
					Monitor.sMsg("Operation Cancelled.", MessageBoxIcon.Information);
				}
			}
			else
			{
				DBOCollection.CreateDataBase();
			}
			
		}
	}


	
}